package nosql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import nosql.query.PropertyInsertException;



/**
 * MysqlConnector realizes the function of connect to mysql 
 * and do some initialization work for dynamic query.
 * @author  Yexi Jiang <http://sites.google.com/site/yexijiang/> *
 */
public class MysqlConnector {
	private static MysqlConnector instance = null;
	
	private String url;
	private String username;
	private String password;
	
	private Connection mysqlConnection;
	
	public static MysqlConnector getInstance(String url, String username, String password){
		if(instance == null){
			instance = new MysqlConnector(url, username, password);
		}
		return instance;
	}
	
	protected MysqlConnector(String url, String username, String password){
		this.url = url;
		this.username = username;
		this.password = password;
		
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			mysqlConnection = DriverManager.getConnection(url, username, password);
			mysqlConnection.setAutoCommit(false);
			init();
		} catch(ClassNotFoundException e){
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Initialize the nosql query system. Create necessary auxiliary tables: metadata table $ 
	 * that stores information about which id contains which property; the query history table '*',
	 * which stores the query history by the query components.
	 * 
	 * @throws SQLException
	 */
	public void init() throws SQLException{
		String createEntityRecordSql = "create table if not exists $(entityId varchar(128) not null," 
			+ " properties text not null, primary key (entityId), index idx_entity(entityId))Engine=InnoDB, Charset=utf8;";
//		String createPropertyAdjacencyListSql = "create table if not exists _(property varchar(128) not null,"
//			+ " relatedProperty text, primary key (property)"
//			+ ")Engine=InnoDB, Charset=utf8";
		String createQueryHistorySql = "create table if not exists `*`(" 
			+ "firstProperty varchar(255), secondProperty varchar(255), type int(1), occurrence int(10), primary key (firstProperty, secondProperty, type), index idx_edge(firstProperty, secondProperty))Engine=InnoDB, Charset=utf8;";
	
		Statement stmt = this.getConnection().createStatement();
		stmt.execute(createEntityRecordSql);
//		stmt.execute(createPropertyAdjacencyListSql);
		stmt.execute(createQueryHistorySql);
		stmt.close();
	}
	
	public Connection getConnection(){
		return mysqlConnection;
	}
	
	/**
	 * Get the names of all the property tables
	 * @return the set of names
	 */
	public Set<String> getAllPropertyTableName(){
		Set<String> setTables = new HashSet<String>();
		try{
			DatabaseMetaData metaData = mysqlConnection.getMetaData();
			ResultSet tableRs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
			while(tableRs.next()){
				String tableName = tableRs.getString("TABLE_NAME");
				if(!tableName.equals("$") && !tableName.equals("_") && !tableName.equals("*"))
					setTables.add(tableName);
			}

		}catch(SQLException e){
			e.printStackTrace();
		}
		return setTables;
	}
	
	/**
	 * Get the names of all property tables that are related to the specified entityId
	 * @param entityId
	 * @return
	 * @throws SQLException 
	 */
	public Set<String> getAllPropertyTableNameByEntityId(String entityId) throws SQLException{
		Set<String> setTables = new HashSet<String>();
		String queryTableString = "select properties from $ where entityId='" + entityId + "';";
		Statement stmt = mysqlConnection.createStatement();
		ResultSet resultSet = stmt.executeQuery(queryTableString);
		if(!resultSet.next())
			return setTables;
		else{
			String[] names = resultSet.getString("properties").split(" ");
			for(String name : names)
				setTables.add(name);
		}
		return setTables;
	}
	
	/**
	 * Add new property into database.
	 * @param propertyName
	 * @throws NosqlException
	 */
	public void addProperty(String propertyName) throws PropertyInsertException, SQLException{
		
		Statement stmt = mysqlConnection.createStatement();
		String insertNewPropertyString = "create table `" + propertyName 
											+ "`(entityId varchar(128) not null,"
//											+ " propertyId int(10) not null," 
//											+ " type int(2) not null,"
											+ " ref text,"
											+ " primary key(entityId),"
											+ " index idx_" + propertyName + "(entityId)"
											+ ")Engine=InnoDB, Charset=utf8;";
		if(getAllPropertyTableName().contains(propertyName))
			throw new PropertyInsertException("property already exists.");
		try{
			stmt.execute(insertNewPropertyString);
//			System.out.println("\t" + insertNewPropertyString);
			stmt.close();
		}catch(SQLException e){
			mysqlConnection.rollback();
			e.printStackTrace();
		}
	}
	
	/**
	 * Add all new properties in set.
	 * @param set a collection of all features, may contains new property name.
	 * @param builder builder that store the property names
	 * @throws NosqlException
	 * @throws SQLException 
	 */
	public void addProperties(Set<String> set, StringBuilder builder) throws NosqlException, SQLException{
		Set<String> originalFeaturesSet = this.getAllPropertyTableName();
		for(String propertyName : set){
			builder.append(propertyName);
			builder.append(" ");
			if(originalFeaturesSet.contains(propertyName.toLowerCase()) == false){
				this.addProperty(propertyName.toLowerCase());
				originalFeaturesSet.add(propertyName.toLowerCase());
			}
		}
	}
	
	public void close() throws SQLException{
		mysqlConnection.close();
	}
	
}
